package dbmanager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import framework.FoodItem;

public class FoodManager extends DatabaseObjectManager {
	private static HashMap<String, FoodItem> foodMap = new HashMap<String, FoodItem>();
	
	public static ArrayList<FoodItem> fetchFoodItemsSuitingPreferences(
			String name, boolean viewApprovedOnly) throws SQLException {
		ResultSet srs = dbm
				.sendQuery("select food_item_name from pref join food_pref_about_food_item "
						+ "ON pref.id = food_pref_about_food_item.food_pref_id where is_liked=0 "
						+ "AND user_id = " + AccountManager.getLogin().getId());
		
		ArrayList<FoodItem> foodItemList = new ArrayList<FoodItem>();
		while (srs.next()) {
			foodItemList.add(new FoodItem(srs));
		}

		return foodItemList;
	}

	public static ArrayList<FoodItem> fetchFoodItems(boolean viewApprovedOnly)
			throws SQLException {
		return fetchFoodItems("", viewApprovedOnly);
	}

	public static ArrayList<FoodItem> fetchFoodItems(String name,
			boolean viewApprovedOnly) throws SQLException {
		String query = "select * from food_item where name like '%" + name
				+ "%' ";

		if (viewApprovedOnly) {
			query += "and approved=1";
		}
		query += " order by name";
		ResultSet srs = dbm.sendQuery(query);

		ArrayList<FoodItem> foodItemList = new ArrayList<FoodItem>();
		while (srs.next()) {
			foodItemList.add(new FoodItem(srs));
		}

		return foodItemList;
	}

	public static FoodItem getFoodItem(String foodName) throws SQLException {
		// check if this food item has been cached
		if (!foodMap.containsKey(foodName)) {
			// if it hasn't, create the new food item and cache it
			ResultSet foodItemResultSet = dbm
					.sendQuery("select * from food_item where name='"
							+ foodName + "'");
			foodItemResultSet.first();

			long creationDate = DatabaseObjectManager
					.stringToDate(foodItemResultSet.getString("creation_date"));
			String unit = foodItemResultSet.getString("unit");
			boolean approved = foodItemResultSet.getString("approved").equals(
					"1") ? true : false;

			FoodItem foodItem = new FoodItem(foodName, unit, creationDate,
					approved);
			foodMap.put(foodName, foodItem);

			return foodItem;
		} else {
			return foodMap.get(foodName);
		}
	}

	public static void approveFoodItem(FoodItem foodItem) throws SQLException {
		dbm.sendInsertQuery("update food_item set approved=1 where name="
				+ foodItem.getName());
	}

	public static boolean foodItemExists(FoodItem fi) throws SQLException {
		return foodItemExists(fi.getName());
	}

	public static boolean foodItemExists(String foodName) throws SQLException {
		ResultSet rs = dbm.sendQuery("select * from food_item where name='"
				+ foodName + "'");
		if (rs.next()) {
			return true;
		} else {
			return false;
		}
	}

	public static boolean addFoodItem(FoodItem fi) throws SQLException {
		return addFoodItem(fi.getName(), fi.getUnit());
	}

	public static boolean addFoodItem(String name, String unit)
			throws SQLException {
		try {
			dbm.sendInsertQuery("insert into food_item(name,unit) values('"
					+ name + "','" + unit + "')");
			return true;
		} catch (MySQLIntegrityConstraintViolationException e) {
			return false;
		}
	}

	/*
	 * public static boolean userHasFoodItem(String foodName){ String query =
	 * "select * from food_item where name in (select food_item_name from user_has_food_item where food_item_name "
	 * + "like '%" + name + "%' " + "and user_id=" +
	 * AccountManager.getLogin().getId() + " ";
	 * 
	 * query += " order by food_item_name)"; ResultSet srs =
	 * dbm.sendQuery(query);
	 * 
	 * ArrayList<FoodItem> foodItemList = new ArrayList<FoodItem>(); while
	 * (srs.next()) { foodItemList.add(new FoodItem(srs)); }
	 * 
	 * return foodItemList;
	 * 
	 * }
	 */
	public static void addPersonalFoodItem(FoodItem foodItem, int amount)
			throws SQLException {
		addPersonalFoodItem(foodItem.getName(), amount);
	}

	public static void addPersonalFoodItem(String foodItem, int amount)
			throws SQLException {
		int curr = 0;
		try {
			curr = FoodManager.getAmount(foodItem);
			
		} catch (SQLException e) {
			dbm.sendInsertQuery("insert into user_has_food_item values('"
					+ foodItem + "','" + AccountManager.getLogin().getId() + "','"
					+ amount + "')");
			return;
		}
		if(curr==0){
			dbm.sendInsertQuery("insert into user_has_food_item values('"
					+ foodItem + "','" + AccountManager.getLogin().getId() + "','"
					+ amount + "')");
			return;
		}	
		dbm.sendInsertQuery("update user_has_food_item set amount="+(curr+amount)+" where food_item_name='"+foodItem+"' and user_id="+AccountManager.getLogin().getId());
		
	}

	public static void removePersonalFoodItem(String foodItem, int amount)
			throws SQLException {
		// A trigger will handle deleting the item if the user has 0 amount.
		int curr = getAmount(foodItem);
		if (curr - amount <= 0) {
			dbm
					.sendInsertQuery("delete from user_has_food_item where food_item_name='"
							+ foodItem
							+ "' and user_id="
							+ AccountManager.getLogin().getId());
		}
		dbm.sendInsertQuery("update user_has_food_item set amount="
				+ (curr - amount) + " where food_item_name='" + foodItem
				+ "' and user_id=" + AccountManager.getLogin().getId());
	}

	public static ArrayList<FoodItem> fetchFridgeItems(String foodName)
			throws SQLException {
		String query = "select * from food_item where name in (select food_item_name from user_has_food_item where food_item_name "
				+ "like '%"
				+ foodName
				+ "%' "
				+ "and user_id="
				+ AccountManager.getLogin().getId() + " ";

		query += " order by food_item_name)";
		ResultSet srs = dbm.sendQuery(query);

		ArrayList<FoodItem> foodItemList = new ArrayList<FoodItem>();
		while (srs.next()) {
			foodItemList.add(new FoodItem(srs));
		}

		return foodItemList;
	}

	public static int getAmount(String foodItem) throws SQLException {
		try {
			String query = "select amount from user_has_food_item where user_id="
					+ AccountManager.getLogin().getId() + " and food_item_name='"
					+ foodItem + "'";
	
			ResultSet srs = dbm.sendQuery(query);
			srs.next();
			return Integer.parseInt(srs.getString("amount"));
		} catch (SQLException s){
			return 0;
		}
	}

	public static void main(String[] args) throws SQLException {
		DatabaseObjectManager.connect();
		System.out.println(addFoodItem("lol beef2", "oz"));
	}
}
